package com.gxa.shop.dao.category.impl;

import com.gxa.shop.bean.dto.CategoryQueryPage;
import com.gxa.shop.bean.pojo.Category;
import com.gxa.shop.bean.vo.CategoryOption;
import com.gxa.shop.bean.vo.CategoryVo;
import com.gxa.shop.commons.jdbc.JDBCUtil;
import com.gxa.shop.dao.category.CategoryDao;


import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * com.gxa.shop.bean.dao.impl
 * User: zjy
 * Date: 2024/12/17 11:32
 * motto:   逆水行舟不进则退
 * Description:
 * Version: V1.0
 */
public class CategoryDaoIml implements CategoryDao {

    @Override
    public List<CategoryVo> queryById(String categoryId) {
        String sql = "SELECT c.category_id ,c.category_name ,p.product_id ,p.product_name ,p.product_image ,p.product_color ,p.unit_price FROM category c JOIN product p ON c.category_id = p.category_id WHERE c.category_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), categoryId);
        if(query.size()>0){
            List<CategoryVo> categoryVos = new ArrayList<>();
            for (Map<String, Object> row : query) {
                CategoryVo categoryVo = new CategoryVo();
                categoryVo.setCategoryId((Integer) row.get("category_id"));
                categoryVo.setCategoryName((String) row.get("category_name"));
                categoryVo.setProductId((Integer) row.get("product_id"));
                categoryVo.setProductName((String) row.get("product_name"));
                categoryVo.setProductImage((String) row.get("product_image"));
                categoryVo.setProductColor((String) row.get("product_color"));
                categoryVo.setUnitPrice((Double) row.get("unit_price"));
                categoryVos.add(categoryVo);
            }
            System.out.println("CategoryDaoIml：分类查询成功");
            return  categoryVos;
        }
        System.out.println("CategoryDaoIml：没有查询到分类数据");
        return null;
    }

    @Override
    public int insert(Category category) {
        String  sql="insert  category (category_name) value (?)";
        JDBCUtil  jdbcUtil=new JDBCUtil();
        int update = jdbcUtil.update(sql,
                category.getCategoryName());
        return update;
    }

    @Override
    public int update(Category category) {
        String  sql="update category set  category_name=? where  category_id=?";
        JDBCUtil  jdbcUtil=new JDBCUtil();
        int update = jdbcUtil.update(sql, category.getCategoryName(),category.getCategoryId());
        return update;
    }

    @Override
    public int deleteById(Integer categoryId) {
        String deleteProductsSQL = "DELETE FROM shop.product WHERE category_id = ?";
        String deleteCategorySQL = "DELETE FROM shop.category WHERE category_id = ?";

        JDBCUtil jdbcUtil = new JDBCUtil();

        try {
            // 开始事务
            jdbcUtil.beginTransaction();

            // 删除所有依赖于该分类的商品
            int productsDeleted = jdbcUtil.update(jdbcUtil.getConnection(), deleteProductsSQL, categoryId);

            // 删除分类本身
            int brandsDeleted = jdbcUtil.update(jdbcUtil.getConnection(), deleteCategorySQL, categoryId);

            // 提交事务
            jdbcUtil.endTransaction(true);

            // 返回受影响的行数（可以是产品或品牌的数量）
            return productsDeleted + brandsDeleted;

        } catch (Exception e) {
            // 如果发生异常，则回滚事务
            jdbcUtil.endTransaction(false);
            throw new RuntimeException("删除品牌及其关联商品时发生错误", e);
        } finally {
            jdbcUtil.close(); // 关闭资源
        }
    }

    @Override
    public Long findByTotal(CategoryQueryPage categoryQueryPage) {
        StringBuffer sql = new StringBuffer("select count(*) as ct from category where 1=1");
        List<Object> parameters = new ArrayList<>();
        System.out.println("CategoryName: '" + categoryQueryPage.getCategoryName() + "'");

        // 验证是否为空  null  "    "
        if (categoryQueryPage.getCategoryName() != null && !categoryQueryPage.getCategoryName().trim().isEmpty()) {
            parameters.add(categoryQueryPage.getCategoryName());
            sql.append(" and category_name like concat('%', ?, '%')");
        }
        System.out.println("sql: " + sql);
        System.out.println("参数: " + parameters);

        JDBCUtil jdbcUtil = new JDBCUtil();
        try {
            List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());
            Long ct = 0L;
            if (query.size() > 0) {
                ct = (Long) query.get(0).get("ct");
            }
            return ct;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("查询分类总数时发生错误: " + e.getMessage());
            return 0L;
        }
    }

    @Override
    public List<Category> findByPage(CategoryQueryPage categoryQueryPage) {
        StringBuffer sql=new StringBuffer("select category_id,category_name from category where 1=1");
        List<Object>  parameters=new ArrayList<>();
        if(categoryQueryPage.getCategoryName()!=null){
            parameters.add(categoryQueryPage.getCategoryName());
            sql.append(" and  category_name like  concat('%',?,'%')");
        }
        sql.append("  order by category_id  asc");
        sql.append("  limit  "+((categoryQueryPage.getPage()-1)*categoryQueryPage.getLimit())
                +","+categoryQueryPage.getLimit());
        System.out.println("sql:"+sql);
        System.out.println("参数:"+parameters);
        JDBCUtil jdbcUtil=new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());
        if(query.size()>0){
            List<Category> list=new ArrayList<>();
            for (Map<String, Object> row : query) {//Community
                Category  category=new Category();
                category.setCategoryId((Integer) row.get("category_id"));
                category.setCategoryName((String) row.get("category_name"));
                list.add(category);
            }
            return  list;
        }

        return null;
    }

    @Override
    public List<CategoryOption> findAllList() {
        String sql="select category_id,category_name from category";
        JDBCUtil jdbcUtil=new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql);
        if(query.size()>0){
            List<CategoryOption> list=new ArrayList<>();
            for (Map<String, Object> row : query) {
                CategoryOption  categoryOption=new CategoryOption();
                categoryOption.setCategoryId((Integer) row.get("category_id"));
                categoryOption.setCategoryName((String) row.get("category_name"));
                list.add(categoryOption);
            }
            return  list;
        }
        return null;
    }
}